// routes/dashboard.js
import express from 'express'
import db from '../db/mysql.js'
import {getDateRange, roundTo2} from '../dingtalk/utils.js'

const router = express.Router()
// 辅助函数：分钟转小时字符串
function minutesToText(minutes) {
    const hours = Math.floor(minutes / 60);
    const mins = minutes % 60;
    return hours ? `${hours}小时${mins}分钟` : `${mins}分钟`;
}



// 2. Dashboard 数据
router.post('/datas', async (req, res) => {
    try {
        // ✅ 1️⃣ 改成从 body 获取参数
        const {nickname, date_or_month, star_parm, end_parm} = req.body;

        /*
            mysql 条件字段：
               douyin_nickname ,date,month
            如果
             nickname 不为空，douyin_nickname不用判断
             如果
             date_or_month == date ，用 date 判断
             date_or_month == month ，用 month 判断
         */
        let sql = `
SELECT douyin_nickname, anchor,
             SUM(daily_gmv) AS sum_gmv,
             SUM(daily_cost) AS sum_cost,
             SUM(daily_gmv) AS sum_daily_gmv,
             SUM(order_subsidy) AS sum_order_subsidy,
             SUM(daily_cost) AS sum_daily_cost,
             SUM(hanging_fee) AS sum_hanging_fee,
             IF((SUM(daily_cost)+SUM(hanging_fee))>0,ROUND((SUM(daily_gmv)-SUM(order_subsidy))/(SUM(daily_cost)+SUM(hanging_fee)),2),0) AS sum_actual_roi,
             SUM(daily_live_mins) AS sum_live_mins
      FROM dd_self_live_summary
      WHERE 1=1
    `;

        // 参数数组
        const params = [];

        // 2️⃣ nickname 条件（不为空才过滤）
        if (nickname) {
            sql += ` AND douyin_nickname = ?`;
            params.push(nickname);
        }

        // 3️⃣ 时间字段选择
        if (date_or_month === "date") {
            sql += ` AND \`date\` BETWEEN ? AND ?`;
            params.push(star_parm, end_parm);
        } else if (date_or_month === "month") {
            sql += ` AND \`month\` BETWEEN ? AND ?`;
            params.push(star_parm, end_parm);
        }

        // 分组排序
        sql += ` GROUP BY douyin_nickname, anchor ORDER BY sum_gmv DESC;`;
        // console.log(sql)
        // console.log(params)
        // ✅ 查询执行
        const zhi_bo_datas = await db.query(sql, params);

        // ✅ 数据分组 + 转换
        const grouped = {};
        zhi_bo_datas.forEach((data) => {
            const douyin_nickname = data.douyin_nickname;
            if (!grouped[douyin_nickname]) grouped[douyin_nickname] = [];

            const sum_live_mins = data.sum_live_mins;
            const sum_live_mins_ch = minutesToText(sum_live_mins);
            data.sum_live_mins_ch = sum_live_mins_ch;
            grouped[douyin_nickname].push(data);
        });

//         const data = grouped;
//         // ✅ 1️⃣ 先对每个店铺内部排序（数组内降序）
//         for (const key in data) {
//             data[key].sort((a, b) => parseFloat(b.sum_gmv) - parseFloat(a.sum_gmv));
//         }
//
// // ✅ 2️⃣ 计算每个店铺的 sum_gmv 总和
//         const totals = Object.entries(data).map(([key, arr]) => {
//             const totalGMV = arr.reduce((sum, item) => sum + parseFloat(item.sum_gmv || 0), 0);
//             return {key, totalGMV};
//         });


        // // ✅ 3️⃣ 按总和降序排序整个对象
        // const sorted = Object.fromEntries(
        //     totals
        //         .sort((a, b) => b.totalGMV - a.totalGMV)
        //         .map(({key}) => [key, data[key]])
        // );


        const tableArrs = [
            "ROOKIE官方旗舰店",
            "Levi's李维斯童装集合店",
            "ROOKIE官方旗舰店直播号",
            "ROOKIE官旗书包号",
            "特步儿童户外旗舰店",
            "特步儿童户外运动直播间",
            "翼启飞童鞋专营店",
            "ROOKIE卖场店童装直播间",
            "ROOKIE官旗童装号",
            "anta kids潮流旗舰店"

        ];
        const sorted = {};
        // 按顺序写入
        for (const key of tableArrs) {
            sorted[key] = grouped[key] ?? []; // 如果不存在，则写入空数组
        }

        res.json(sorted);
    } catch (err) {
        console.error(err);
        res.status(500).json({error: "数据库查询失败"});
    }
});

router.post('/shop_month_datas', async (req, res) => {
    try {

        let sql = `
        SELECT douyin_nickname ,
        SUM(daily_gmv) AS sum_month_gmv,
        SUM(daily_cost) AS sum_month_cost,
        IF(SUM(daily_cost)>0,ROUND(SUM(daily_gmv)/SUM(daily_cost),2),0) AS month_roi 
         FROM dd_self_live_summary WHERE \`month\`=CONCAT(YEAR(CURDATE()),"-",MONTH(CURDATE())) GROUP BY douyin_nickname;
    `;


        // ✅ 查询执行
        const zhi_bo_datas = await db.query(sql);

        // ✅ 数据分组 + 转换
        const grouped = {};
        zhi_bo_datas.forEach((data) => {
            const douyin_nickname = data.douyin_nickname;
            grouped[douyin_nickname] = data;
        });

        res.json(grouped);
    } catch (err) {
        console.error(err);
        res.status(500).json({error: "数据库查询失败"});
    }
});





export default router;
